package com.qs.imp;

import java.sql.Connection; 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.qs.dao.ConnectionManager;
import com.qs.entity.Daily;
import com.qs.inter.DailyInt;

public class DailyImp implements DailyInt {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;

	
	public List<Daily> queryByPage(int pageSize, int pageNow) throws Exception {
		conn = ConnectionManager.getConnction();
		String sql = "select top " + pageSize
				+ " * from Daily where DailyId not in(select top "
				+ (pageNow * pageSize - pageSize) + " DailyId from Daily ) ";
		List<Daily> list = new ArrayList<Daily>();
		try {
			if (conn != null && pageSize > 0 && pageNow > 0) {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();

				while (rs.next()) {
					Daily daily = new Daily();
					daily.setDailyId(rs.getInt("DailyId"));
					daily.setTitle(rs.getString("Title"));
					daily.setUserId(rs.getInt("UserId"));
					daily.setContent(rs.getString("Content"));
					daily.setDateline(rs.getDate("dateline"));
					daily.setLastpostdate(rs.getDate("lastpostdate"));
					list.add(daily);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public List<Daily> fqueryByPage(int pageSize, int pageNow,int id) throws Exception {
		conn = ConnectionManager.getConnction();
		String sql = "select top " + pageSize
				+ " * from Daily where DailyId not in(select top "
				+ (pageNow * pageSize - pageSize) + " DailyId from Daily where UserId="+id+" ) and UserId="+id+"";
		List<Daily> list = new ArrayList<Daily>();
		try {
			if (conn != null && pageSize > 0 && pageNow > 0) {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();

				while (rs.next()) {
					Daily daily = new Daily();
					daily.setDailyId(rs.getInt("DailyId"));
					daily.setTitle(rs.getString("Title"));
					daily.setUserId(rs.getInt("UserId"));
					daily.setContent(rs.getString("Content"));
					daily.setDateline(rs.getDate("dateline"));
					daily.setLastpostdate(rs.getDate("lastpostdate"));
					list.add(daily);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public int countmax(int id) throws Exception {
		int countmax = 0;
		String sql = "select count(DailyId) from Daily where UserId="+id+"";
		conn = ConnectionManager.getConnction();
		try {
			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			countmax = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return countmax;
	}
	
	public int addDaily(Daily daily, int UserId){
		int i = 0;
		String sql = "INSERT INTO Daily(UserId,Title,Content,dateline,lastpostdate)values(?,?,?,?,?)";
		

		try {
			conn = ConnectionManager.getConnction();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, UserId);
			ps.setString(2, daily.getTitle());
			ps.setString(3, daily.getContent());
			ps.setDate(4, daily.getDateline());
			ps.setDate(5, daily.getDateline());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
		return i;
	}

	public int deleteDaily(Daily daily) throws Exception {
		conn = ConnectionManager.getConnction();
		String sql = "";
		try {

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;

	}

	public Daily selectDaily(int dailyid) {
		Daily daily = new Daily();
		String sql = "select * from Daily where DailyId =" + dailyid;
		try {
			conn = ConnectionManager.getConnction();
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			if (rs.next()) {
				daily.setDailyId(rs.getInt("DailyId"));
				daily.setTitle(rs.getString("Title"));
				daily.setUserId(rs.getInt("UserId"));
				daily.setContent(rs.getString("Content"));
				daily.setDateline(rs.getDate("dateline"));
				daily.setLastpostdate(rs.getDate("lastpostdate"));

			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return daily;
	}

	public int updateDaily(Daily daily) {
		int i=0;
		 String sql="UPDATE Daily SET Title=?,Content=? where DailyId=?";
		try {
			conn = ConnectionManager.getConnction();
			ps = conn.prepareStatement(sql);
			ps.setString(1, daily.getTitle());
			ps.setString(2, daily.getContent());
			ps.setInt(3, daily.getDailyId());
			i = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}
public static void main(String[] args) throws Exception {
	int i=new DailyImp().countmax(30);
	System.out.println(i);
	
}
	public int selectDaily1(int dailyid) {
		String sql="select * from Daily where DailyId=?";
		try {
			conn = ConnectionManager.getConnction();
			
			ps=conn.prepareStatement(sql);
			ps.setInt(1, dailyid);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public int deleteDaily(int dailyid){
		int i=0;
		 String sql="delete from Daily where DailyId="+dailyid;
		 try {
			conn = ConnectionManager.getConnction();
			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

}
